import xlrd
from sqlalchemy import create_engine
from datetime import date
import pandas as pd
import re
import os


def filter_sheetnames(sheetnames, filteredlist):
    for item in sheetnames:
        if 'OP' in item:
            filteredlist.append(item)


path = input('PCP所在路径: ')+r'/'
# os.makedirs(path+'PCP导入用', exist_ok=True)
# save_path = path+r'PCP导入用\PCP导入用.xlsx'
k = os.listdir(path)
fl = []
i = 0
for fn in k:
    m = re.findall('.xlsx$', fn)
    if (m):
        fl.append(fn)
        i = i+1
dfl = []
for file_name in fl:
    print(file_name)

    # 表单输入内容
    editor = 'admin'
    stage = '生产'

    # Excel处理
    work_book = xlrd.open_workbook(path+file_name)
    sheet_list = work_book.sheet_names()
    print(sheet_list)

    filteredlist = []
    filter_sheetnames(sheet_list, filteredlist)

    for sheet in filteredlist:
        print(sheet)
        sh = work_book.sheet_by_name(sheet)
        # print(sheet)

        # 表头
        op = sheet
        # 获取零件名称的值
        p = sh.cell(7, 0).value
        print(p)
        if 'N15' in p or "B15" in p:
            print("yes")

            partName = 'N15'
            partType = 'N15/N15A'
        else:
            partName = 'N12'
            if 'N12' in p and 'N12A' in p:
                partType = 'N12/N12A'
            elif 'N12A' in p:
                partType = 'N12A'
            else:
                partType = 'N12'

        partNumber = sh.cell(5, 0).value
        number = sh.cell(3, 0).value.split(':')[1]
        # proof=sh.cell(3, 12).value
        try:
            if type(sh.cell(3, 12).value) == float:
                proof = xlrd.xldate_as_datetime(sh.cell(3, 12).value, 0)
            else:
                dat = sh.cell(3, 12).value.split('.')
                proof = date(int(dat[0]), int(dat[1]), int(dat[2]))
        except:
            proof = None
        coreTeam = sh.cell(5, 5).value
        audit = sh.cell(9, 5).value
        # print(op,partName,partType,partNumber,number)

        # PCP内容
        merged = sh.merged_cells
        print(merged)
        for r in range(12, sh.nrows):
            print("第" + str(r) + "次")
            if sh.cell(r, 0).value == '附页－反应计划':
                break
            li = [op, partName, partType, partNumber,
                  number, proof, coreTeam, audit, stage]
            # 对于产品过程/规格公差列占据H\I两列的，去掉列表中的8列，增加到13列，对于产品过程/规格公差列占据H一列的，保留列表中的8列，删除13列
            for c in [0, 1, 2, 3, 4, 5, 6, 7, 9, 10, 11, 12, 13]:
                if c == 0:
                    if type(sh.cell(r, c).value) == float:
                        v = str(int(sh.cell(r, c).value))
                    else:
                        v = sh.cell(r, c).value
                else:
                    v = sh.cell(r, c).value
                    if type(v) == str:
                        v = v.replace(' ', '')

                # 处理合并单元格
                if v is None or v == '':
                    for (rlow, rhigh, clow, chigh) in merged:
                        if rlow <= r < rhigh:
                            if clow <= c < chigh:
                                v = sh.cell(rlow, clow).value

                li.append(v)
            dfl.append(li)

# print(dfl)
df = pd.DataFrame(data=dfl, columns=['op', 'partName', 'partType', 'partNumber', 'number', 'proof', 'coreTeam', 'audit', 'stage', 'proNo',
                  'operation', 'equipment', 'charNo', 'product', 'process', 'charClass', 'standard', 'measure', 'sampleSize', 'sampleFreq', 'control', 'react'])
df = df.dropna(subset=['op'])
print(df)
conn = create_engine(
    'mysql+pymysql://root:123456@10.64.242.101/pfmea_pcp?charset=utf8')
df.to_sql('powertrain_aspcp', con=conn, schema='pfmea_pcp',
          index=False, index_label=False, if_exists='append', chunksize=1000)
